package data;

import java.util.List;

import models.Task;

import org.sql2o.Sql2o;

public class TaskDAO {
	private static Sql2o database;
	static {
		database = new Connection().open();
	}
	
	public void createTaskTable(){
		String sql = "create table Task(id int auto_increment primary key,name varchar(50),description varchar(200))";
		database.createQuery(sql).executeUpdate();
	}
	
	public void dropTaskTable() {
		String sql = "Drop table Task";
		database.createQuery(sql).executeUpdate();
	}
	
	public void delete(int id) {
		String sql = "delete from Task where id = :id";
		database.createQuery(sql)
				.addParameter("id", id).executeUpdate();
		
	}
	
	public Task get(int id) {
		String sql = "select * from Task where id = :id";
		return database.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Task.class);
	}
	
	public List<Task> list() {
		String sql = "select * from Task";
		return database.createQuery(sql).executeAndFetch(Task.class);
	}
	
	public Object insert(String name, String description){
		String sql = "insert into Task(name, description) values(:name, :description)";
		Object result = database.createQuery(sql)
			.addParameter("name", name)
			.addParameter("description", description)
			.executeUpdate().getKey();
		return result;
	}
	
	public void update(int id, String name, String description){
		String sql = "update Task set name = :name, description = :description where id = :id";
		database.createQuery(sql)
				.addParameter("name", name)
				.addParameter("description", description)
				.addParameter("id", id)
				.executeUpdate().getKey();
	}
	
}